﻿-- =============================================
-- Author:		Taylor Chase
-- Create date: December 10 2015
-- Description:	Checks to see if the requesting entity can edit/view the requested entity. 
--				PermissionLevel contains 0-5 representing the security relationship between the requesting
--				and requested entity.  
--				DeniedAttributes contains a comma-separated list of attributes that the entity is not allowed to modify.
-- =============================================
CREATE FUNCTION [dbo].[_OLD_ViewEntitySecurity] 
(
	-- Add the parameters for the function here
	@RequestorEntityID int, 
	@RequestedEntityID int,
	@LowestPermLevel int = 5,
	@ChainSoFar nvarchar(4000) = null

)
RETURNS 
@ReturnedEntities TABLE 
(
	-- Add the column definitions for the TABLE variable here
	PermissionLevel int, 
	TopShortRelationshipName nvarchar(50),
	DeniedAttributes nvarchar(2000)
)
AS
BEGIN
	DECLARE @E1ID int, @E2ID int, @ShortRelationshipName nvarchar(255),@IsRevRelationship bit, 
					   @StartDate datetime, @EndDate datetime, 
					   @SuperRelationship nvarchar(255), @E1CAE2 int, @E1CACE2 int
	DECLARE @ReturnValue int,@RetSpecialAttributes nvarchar(1000)
	DECLARE @ThisLevelChainSoFar nvarchar(4000)
	DECLARE @PermissionLevel int, @TopShortRelationshipName nvarchar(255),
		@DeniedAttributes nvarchar(1000)

	IF @ChainSoFar IS NULL OR @ChainSoFar = ''
	BEGIN
		SET @ChainSoFar = ';'
	END

	DECLARE @MyCursor CURSOR
	SET @MyCursor = CURSOR FAST_FORWARD
	FOR -- This query gets the union of all forward and reverse relationships.  It then filters the 
		-- query based on the comma-separated list of IDs that are passed from the previous
		-- recursive call.  If E2ID is already part of the string, then filter it out of the
		-- security table.
		SELECT * FROM ViewEntityRelationships(@RequestorEntityID,DEFAULT,@ChainSoFar)	

	OPEN @MyCursor
	FETCH NEXT FROM @MyCursor
	INTO @E1ID, @E2ID, @ShortRelationshipName, @IsRevRelationship, @StartDate, @EndDate, 
						@SuperRelationship, @E1CAE2, @E1CACE2
	WHILE @@FETCH_STATUS = 0
	BEGIN
		-- If the row is part of a super-relationship, check the permissions assigned to the 
		-- super-relationship (this function checks with the first level as well, so
		-- permissions assigned to the lower relationship will not be lost)
		IF NOT(@SuperRelationship IS NULL) BEGIN
			SET @E1CAE2 = dbo.ViewEntitySuperRelationshipPermission(@ShortRelationshipName,@IsRevRelationship,'Request')
			SET @E1CACE2 =  dbo.ViewEntitySuperRelationshipPermission(@ShortRelationshipName,@IsRevRelationship,'RequestChildren')
		END
		IF @E1CAE2 IS NULL
			SET @E1CAE2 = 0
		IF @E1CACE2 IS NULL
			SET @E1CACE2 = 0

		-- E2ID is the ID whose data needs to be viewed or modified.  
		-- If it is the 2nd ID, then check permissions
		IF @E2ID = @RequestedEntityID
		BEGIN
			--INSERT INTO @ReturnedEntities(PermissionLevel,TopShortRelationshipName,DeniedAttributes)
			--VALUES(0,'DEBUGGING-FOUND',cast(@E1CAE2 AS nvarchar(10))+@ShortRelationshipName+cast(@IsRevRelationship as varchar(1)))
			--RETURN
			IF @E1CAE2 > 0 BEGIN
				-- This inserts the current Requested ID into a table by itself and returns that.
				INSERT INTO @ReturnedEntities(PermissionLevel,TopShortRelationshipName,DeniedAttributes)
				VALUES(@E1CAE2,@ShortRelationshipName,'')
			END
		END
		ELSE IF @RequestorEntityID <> @E2ID BEGIN
			-- If the relationship is not directly with RequestedEntityID, then call this function 
			-- passing in E2ID as the new RequestorEntityID if E1CACE2 is greater than 0
			SET @ThisLevelChainSoFar = @ChainSoFar + cast(@E1ID as varchar(20)) + ';'

			--INSERT INTO @ReturnedEntities(PermissionLevel,TopShortRelationshipName,DeniedAttributes)
			--VALUES(0,'DEBUGGING-NOTFOUND',@ThisLevelChainSoFar + coalesce(cast(@E1CACE2 AS nvarchar(10)),'')+@ShortRelationshipName)

			IF @E1CACE2 > 0
			BEGIN
				INSERT INTO @ReturnedEntities(PermissionLevel,TopShortRelationshipName,DeniedAttributes)
				SELECT IIF(PermissionLevel<@E1CACE2,PermissionLevel,@E1CACE2),@ShortRelationshipName,DeniedAttributes
				FROM ViewEntitySecurity(@E2ID,@RequestedEntityID,DEFAULT,@ThisLevelChainSoFar)
			END
			--ELSE
			--	INSERT INTO @ReturnedEntities(PermissionLevel,TopShortRelationshipName,DeniedAttributes)
			--	VALUES(0,'STOP',@ThisLevelChainSoFar + coalesce(cast(@E1CACE2 AS nvarchar(10)),'')+@ShortRelationshipName)

		END
		-- No final else, because SELF relationships should not be followed.

		FETCH NEXT FROM @MyCursor
		INTO @E1ID, @E2ID, @ShortRelationshipName, @IsRevRelationship, @StartDate, @EndDate, 
						   @SuperRelationship, @E1CAE2, @E1CACE2
	END
	CLOSE @MyCursor
	DEALLOCATE @MyCursor

	-- The if statement restricts this so it only runs at the top level of the recursive function.
	IF @ChainSoFar = ';' BEGIN
		-- All paths now need to be expanded to all superrelationship entries.
		DECLARE @ExpansionCursor CURSOR
		SET @ExpansionCursor = CURSOR FAST_FORWARD
		FOR 
			SELECT * FROM @ReturnedEntities ORDER BY TopShortRelationshipName
		
		OPEN @ExpansionCursor
		FETCH NEXT FROM @ExpansionCursor
		INTO @PermissionLevel, @TopShortRelationshipName, @DeniedAttributes
		WHILE @@FETCH_STATUS = 0
		BEGIN
			INSERT INTO @ReturnedEntities(TopShortRelationshipName,PermissionLevel,DeniedAttributes)
			SELECT ShortRelationshipName,@PermissionLevel,''
			FROM ViewSuperRelationships(@TopShortRelationshipName)

			FETCH NEXT FROM @ExpansionCursor
			INTO @PermissionLevel, @TopShortRelationshipName, @DeniedAttributes
		END
		CLOSE @ExpansionCursor
		DEALLOCATE @ExpansionCursor

		SET @DeniedAttributes=STUFF((SELECT ',' + AttributeName
		FROM Entities 
			LEFT JOIN DefinedSecurityAttributeAccess DSAA 
			ON  (Entities.EntityType = DSAA.EntityType AND (Entities.EntitySubType = DSAA.EntitySubtype OR DSAA.EntitySubtype IS NULL))
			LEFT JOIN DefinedAttributes DA ON DSAA.AttributeID = DA.AttributeID
		WHERE EntityID = @RequestedEntityID AND AttributeName NOT IN
		(SELECT AttributeName
		FROM Entities 
			LEFT JOIN DefinedSecurityAttributeAccess DSAA 
			ON  (Entities.EntityType = DSAA.EntityType AND (Entities.EntitySubType = DSAA.EntitySubtype OR DSAA.EntitySubtype IS NULL))
			LEFT JOIN DefinedAttributes DA ON DSAA.AttributeID = DA.AttributeID
		WHERE DSAA.ShortRelationshipName IN (SELECT TopShortRelationshipName FROM @ReturnedEntities) AND EntityID = @RequestedEntityID) FOR XML Path('')),1,1,'')

		UPDATE @ReturnedEntities SET DeniedAttributes = @DeniedAttributes


		---- This loops through the table of EntityIDs and determines the attributes that the Requestor is not
		---- allowed to access. 
		--DECLARE @FinalCursor CURSOR
		--SET @FinalCursor = CURSOR FAST_FORWARD
		--FOR 
		--	SELECT * FROM @ReturnedEntities ORDER BY TopShortRelationshipName
		
		--OPEN @FinalCursor
		--FETCH NEXT FROM @FinalCursor
		--INTO @PermissionLevel, @TopShortRelationshipName, @DeniedAttributes
		--WHILE @@FETCH_STATUS = 0
		--BEGIN
		--	Update @ReturnedEntities 
		--		SET DeniedAttributes=STUFF((SELECT ',' + AttributeName
		--		FROM Entities 
		--			LEFT JOIN DefinedSecurityAttributeAccess DSAA 
		--			ON  (Entities.EntityType = DSAA.EntityType AND (Entities.EntitySubType = DSAA.EntitySubtype OR DSAA.EntitySubtype IS NULL))
		--			LEFT JOIN DefinedAttributes DA ON DSAA.AttributeID = DA.AttributeID
		--		WHERE EntityID = @RequestedEntityID AND AttributeName NOT IN
		--		(SELECT AttributeName
		--		FROM Entities 
		--			LEFT JOIN DefinedSecurityAttributeAccess DSAA 
		--			ON  (Entities.EntityType = DSAA.EntityType AND (Entities.EntitySubType = DSAA.EntitySubtype OR DSAA.EntitySubtype IS NULL))
		--			LEFT JOIN DefinedAttributes DA ON DSAA.AttributeID = DA.AttributeID
		--		WHERE DSAA.ShortRelationshipName IN (SELECT TopShortRelationshipName FROM @ReturnedEntities) AND EntityID = @RequestedEntityID) FOR XML Path('')),1,1,'')
		--	WHERE TopShortRelationshipName = @TopShortRelationshipName

		--	FETCH NEXT FROM @FinalCursor
		--	INTO @PermissionLevel, @TopShortRelationshipName, @DeniedAttributes
		--END
		--CLOSE @FinalCursor
		--DEALLOCATE @FinalCursor
	END			


	
	RETURN 
END